#!C:\perl\bin\perl.exe
use strict;
use warnings;
use CGI qw(:standard);
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use List::Compare;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});
# execute INSERT query

# ************************************* Création type_test ************************************* #

my @unite_mesure=('T1','T2','T3','T4','T5','T6','Temps');
my @id_type_test;
my $row=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='3PRT' AND unite_mesure=?");
foreach my $elt(@unite_mesure){
	$row->execute($elt);
	while(my @unite=$row->fetchrow_array()){
		push(@id_type_test,$unite[0]);
	}
	$row->finish();
}
if(not @id_type_test){
	foreach my $elt(@unite_mesure){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', '3PRT', '$elt')"); 
		$row->execute($elt);
		while(my @unite=$row->fetchrow_array()){
			push(@id_type_test,$unite[0]);
		}
		$row->finish();
	}
}

my $fichier=shift;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;

foreach($workbook->worksheets()){
    $worksheet=$_;

	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();

	for my $row (1 .. $row_max){ #parcours lignes
		my $micro;
		my $exper;
		my $date_test;
		my @test;
		
		for my $col ($col_min .. $col_max){ #parcours colonnes
			if ($col eq 0) { #recup num microcebe
				my $cont=$worksheet->get_cell($row,$col);
				if($cont){ $micro=$cont->value(); }
			}
			if($micro){
				if ($col eq 1) { #recup date test
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){$date_test=$cont->value();}
					
				}
				if ($col>=2 and $col<=8) { #recup T1,T2,T3,T4,T5,T6,tps
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){
						push (@test,$cont->value());
					}else{
						push(@test,'');
					}
				}
				if($col eq 11 and @test){ #expérimentateur
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){$exper=$cont->value();}
				}
			}
		}
		if($micro){
			#~ print "$micro, $exper, $date_test :\n";
			#~ foreach (@test){
				#~ print "$_, ";
			#~ }
			#~ print "\n";
			
	
			# ************************************* Création animal ************************************* #
			my $id_anim;
			
			### verification de l'existence de l'animal dans la base
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$micro'");

			if (not $id_anim){ # si l'animal n'existait pas
				$dbh->do("INSERT INTO Animal(code_animal) VALUES ('$micro')");
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$micro'");
			}
			
			# ************************************* Création test ************************************* #
			my %id_type_test2;
			my %result2;
			my %id_test2;
			
			for my $i(0 .. 6){
				$result2{$i+1}=$test[$i]; 
			}
			
			
			#modification de id_type_test en fonction du nombre de valeurs que contient result2
			foreach my $key(sort keys (%result2)){
				if($result2{$key} ne ''){
					$id_type_test2{$key}=$id_type_test[$key-1];
				}
			}
			
			### verification que le test n'existe pas deja ###
			my @id_type_test3;
			my $prep_test=$dbh->prepare("SELECT Test.id_type_test FROM Test, Type_test WHERE Type_test.description='3PRT' AND Test.id_type_test=Type_test.id_type_test AND experimentateur='$exper' AND date_test=(E'$date_test')");
			my $prep_test2=$dbh->prepare("SELECT id_test FROM Test WHERE experimentateur='$exper' AND Test.id_type_test=? AND date_test=(E'$date_test')");
			$prep_test->execute();
			while (my @val=$prep_test->fetchrow_array()){
				push(@id_type_test3,$val[0]);
			}
			$prep_test->finish();
					
			### ajout du test si il n'existe pas ###
			### attention ici on teste s'il y a des type test avec label + desc = qui existe (mais unite differente)
			print "liste id type: @id_type_test3 pour animal $id_anim";
			if(@id_type_test3){
				my @temp;
				## temp renvoie la liste de type_test qui sont dans le fichier = unite de mesure (par rapport à la liste existante)
				foreach my $z(sort keys %id_type_test2){
					print "$id_type_test2{$z}";
					push(@temp,$id_type_test2{$z});
				}
				if($#id_type_test3<$#temp){
					my $union=List::Compare->new(\@temp, \@id_type_test3);
					my @id_type_test4=$union->get_symmetric_difference;
					foreach my $l(@id_type_test4){
						print "test existe animal $id_anim";
						$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$date_test', '$exper', $l)");
					}
				}
			}
			## si aucun type_test avec le label + desc (comportement 3PRT)
			elsif(not @id_type_test3){
				foreach my $l(sort keys %id_type_test2){
					print "test n existe pas animal $id_anim";
					$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$date_test', '$exper', $id_type_test2{$l})");
				}
			}

			#récupération de l'id_test = hash (id_type_test=>id_test)
			## test correspondant aux types_test de id_type_test2 pour l'expé et la date du fichier et l'animal
			foreach my $k(sort keys %id_type_test2){
				$prep_test2->execute($id_type_test2{$k});
				while(my @val=$prep_test2->fetchrow_array()){
					$id_test2{$k}=$val[0];
				}
				$prep_test2->finish();
			}
			
			# ************************************* Création resultat_test ************************************* #
			my %id_res2;
			
			### vérification que resultat_test n'existe pas ###
			my %id_res3;
			my $prep_rtest=$dbh->prepare("SELECT id_res from resultat_test,animal WHERE resultat_test.id_animal= animal.id_animal AND id_test=? and animal.id_animal=$id_anim");
			foreach my $i(sort keys (%id_test2)){
				$prep_rtest->execute($id_test2{$i});
				while (my @val=$prep_rtest->fetchrow_array()){
					$id_res3{$i}=$val[0];
				}
				$prep_rtest->finish();
			}
			### ajout de resultat_test si il n'existe pas ###
			if(%id_res3){
				my @keys_res=keys %id_res3;
				my @keys_test=keys %id_test2;
				if($#keys_res<$#keys_test){
					my $union=List::Compare->new(\@keys_test, \@keys_res);
					my @comp=$union->get_symmetric_difference; #différence au niveau des clés
					foreach my $l(@comp){
						print "titi: $id_test2{$l}, $id_anim";
						$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2{$l}, $id_anim)");
					}
				}
			}
			elsif(not %id_res3){
				foreach my $i(sort keys (%id_test2)){
					print "toto: $id_test2{$i}, $id_anim";
					$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2{$i}, $id_anim)");
				}
			}
			
			#récupération de l'id_res = hash (id_type_test=>id_res)
			foreach my $cle(keys (%id_test2)){
				$prep_rtest->execute($id_test2{$cle});
				while(my @val=$prep_rtest->fetchrow_array()){
					$id_res2{$cle}=$val[0];
				}
				$prep_rtest->finish();
			}
			
			# ************************************* Création resultat_test_simple ************************************* #
			
			my $prep_resultat=$dbh->prepare("SELECT id_res FROM resultat_test_simple WHERE mesure=? AND id_res=?");

			foreach my $cle(keys (%result2)){
				if($result2{$cle} ne ''){
					foreach my $cle2(keys (%id_res2)){
						if($cle eq $cle2){
							$prep_resultat->execute($result2{$cle},$id_res2{$cle2});
							my @val=$prep_resultat->fetchrow_array();
							my $size3=@val;
							$prep_test->finish();
							if($size3 eq 0){ #si l'animal est resté au centre
								$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure) VALUES($id_res2{$cle2}, $result2{$cle})");					
							}
						}
					}
				}
			}	
		}
	}
	last if $_->get_name() eq 'Feuil1';
}

$dbh->disconnect();
